/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.Customer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import util.DBUtil;

/**
 *
 * @author Noi Nho
 */
public class CustomerDAO {
//     private int customerID;
//    private User user;
//    private String name;
//    private String email;
//    private String mobile;
//    private String address;
//    private int personalNumber;

    String SQL_CREATE_CUSTOMER = "INSERT INTO [customer](username, name, email, mobile, address, personalNumber) VALUES(?, ?, ?, ?, ?, ?)";
    String SQL_READ_ALL_CUSTOMER = "SELECT * FROM [customer]";
    String SQL_UPDATE_CUSTOMER = "UPDATE [customer] SET username =?, name = ?,email = ?, mobile = ?, address = ?, personalnumber = ? WHERE customerid = ? ";
    String SQl_DELETE_CUSTOMER = "DELETE FROM [customer] WHERE customerid = ?";
    String SQL_GET_CUSTOMER_BY_CUSTOMERID = "SELECT * FROM WHERE customertid = ?";
    private Connection conn = null;
    private PreparedStatement pst = null;
    private ResultSet rs = null;
    public String result = "";

    public String createCustomer(Customer cus) {
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(SQL_CREATE_CUSTOMER);
            pst.setString(1, cus.getUser().getUsername());
            pst.setString(2, cus.getName());
            pst.setString(3, cus.getEmail());
            pst.setString(4, cus.getMobile());
            pst.setString(5, cus.getAddress());
            pst.setString(6, cus.getPersonalNumber());
            if (pst.executeUpdate() > 0) {
                result = "Create Customer successfully!";
            } else {
                result = "Create Customer unsuccessfully!";
            }
            pst.close();
        } catch (SQLException ex) {
            Logger.getLogger(TourCateDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtil.closeConn(conn);
        }
        return result;
    }

    public ArrayList<Customer> getAllCustomer() {
        ArrayList<Customer> customers = new ArrayList<Customer>();
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(SQL_READ_ALL_CUSTOMER);
            rs = pst.executeQuery();
            while (rs.next()) {
                Customer cus = new Customer();
                cus.setCustomerID(rs.getInt("CustomerID"));
                String username = rs.getString("Username");
                UserDAO userDAO = new UserDAO();
                cus.setUser(userDAO.getUserByUserName(username));
                cus.setName(rs.getString("Name"));
                cus.setEmail(rs.getString("Email"));
                cus.setMobile(rs.getString("Mobile"));
                cus.setAddress(rs.getString("Address"));
                cus.setPersonalNumber(rs.getString("PersonalNumber"));
                customers.add(cus);
            }
            pst.close();
        } catch (SQLException ex) {
            Logger.getLogger(TourCateDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {

            DBUtil.closeConn(conn);
        }
        return customers;
    }

public Customer getCustomerByID(int cusID) {
        Customer cus = null;
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(SQL_GET_CUSTOMER_BY_CUSTOMERID);
            pst.setInt(1, cusID);
            rs = pst.executeQuery();
            if (rs.next()) {
                cus = new Customer();
                cus.setCustomerID(rs.getInt("CustomerID"));
                String username = rs.getString("Username");
                UserDAO userDAO = new UserDAO();
                cus.setUser(userDAO.getUserByUserName(username));
                cus.setName(rs.getString("Name"));
                cus.setEmail(rs.getString("Email"));
                cus.setMobile(rs.getString("Mobile"));
                cus.setAddress(rs.getString("Address"));
                cus.setPersonalNumber(rs.getString("PersonalNumber"));
                
            }
            pst.close();
        } catch (SQLException ex) {
            Logger.getLogger(TourCateDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {

            DBUtil.closeConn(conn);
        }
        return cus;
    }
     public String updateCustomer(Customer cus) {
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(SQL_UPDATE_CUSTOMER);
            pst.setString(1, cus.getUser().getUsername());
            pst.setString(2, cus.getName());
            pst.setString(3, cus.getEmail());
            pst.setString(4, cus.getMobile());
            pst.setString(5, cus.getAddress());
            pst.setString(6, cus.getPersonalNumber());
            pst.setInt(7, cus.getCustomerID());
            if (pst.executeUpdate() > 0) {
                result = "Update Customer successfully!";
            } else {
                result = "Update Customer unsuccessfully!";
            }
            pst.close();
        } catch (SQLException ex) {
            Logger.getLogger(CustomerDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtil.closeConn(conn);
        }
        return result;
    }
     public String deleteCustomer(int  customerid) {
         try {
             conn = DBUtil.getConn();
             pst = conn.prepareStatement(SQl_DELETE_CUSTOMER);
             pst.setInt(1, customerid);
             if (pst.executeUpdate() > 0) {
                 result = "Delete Customer successfully!";
             } else {
                 result = "Delete Customer unsuccessfully!";
             }
             pst.close();
         } catch (SQLException ex) {
             Logger.getLogger(CustomerDAO.class.getName()).log(Level.SEVERE, null, ex);
         } finally {
             DBUtil.closeConn(conn);
         }
         return result;
    }
}
